前言

在前一章節中，我們介紹了SELECT語句中可以使用的從句以及用法，在本章節繼續介紹另一部分的從句。

JOIN clause

JOIN是使用每個資料表共同有的值組合條件來自一個或多個資料表的欄位並產生一個新資料表。它是支持SQL的資料庫中的常見操作，對應於關係代數JOIN(relational algebra join)。

若一個資料表與自己JOIN的特殊情況通常稱為自連接(self-join)。

相關的語法如下：

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

敘述式中有ON從句中和USING從句有欄位且這些稱為「連接鍵」(jon keys)。除非有其他的說明，否則join會具有符合的連接鍵的欄位產生笛卡爾積，這可能會產生比來源的資料表還要多很多筆數的結果。

所有ClickHouse資料庫支援有關於SQL JOIN標準的種類如下：

• INNER JOIN，只有符合的資料的結果才會回傳。
• LEFT OUTER JOIN，在左邊的資料表中沒有符合的資料與兩個資料表有符合的資料的結果皆會回傳。
• RIGHT OUTER JOIN，在右邊的資料表中沒有符合的資料與兩個資料表有符合的資料的結果皆會回傳。
• FULL OUTER JOIN, 兩者資料表都沒有符合的所有資料與符合的資料的結果都會回傳。
• CROSS JOIN，且沒有指定「連接鍵」(join keys)時候，會對所有的資料表執行笛卡爾積並回傳結果。

沒有指定類型的JOIN則指的是INNER，而OUTER關鍵字則可以安全地忽略。CROSS JOIN的替代語法是在FROM從句中指定多個資料表並用逗號來分隔。

ClickHouse也支援額外的JOIN類型，相觀的類型如下：

• LEFT SEMI JOIN與RIGHT SEMI JOIN，將「連接鍵」(join keys)加入白名單中，並不產生笛卡爾積。
• LEFT ANTI JOIN與RIGHT ANTI JOIN，將「連接鍵」(join keys)加入黑名單中，並不產生笛卡爾積。
• LEFT ANY JOIN、RIGHT ANY JOIN與INNER ANY JOIN，部分（對於LEFT和RIGHT的相反結果）或完全（對於INNER和FULL）並禁用標準JOIN類型的笛卡爾積。
• ASOF JOIN與LEFT ASOF JOIN, 加入不完全符合的資料，ASOF JOIN用法如下面所述。

需要注意的是，當設定檔中設定了partial_merge時，RIGHT JOIN與FULL JOIN只支援在所有嚴格的類型的JOIN，即SEMI、ANTI、ANY以及ASOF是不支援的。

預設的join類型的設定可以透過join_default_strictness的設定進行修改，ANY JOIN的操作在ClickHouse資料庫的行為取決於any_join_distinct_right_table_keys的設定。

與join相關的設定如下：

• join_algorithm
• join_any_take_last_row
• join_use_nulls
• partial_merge_join_optimizations
• partial_merge_join_rows_in_right_blocks
• join_on_disk_max_files_to_merge
• any_join_distinct_right_table_keys

ON的部分可以包含使用AND與OR等邏輯運算符號並組合成多個條件，指定「連接鍵」(join keys)的條件必須同時使用左右資料表，並且必須使用相等的運算符號，其他條件可以使用其他邏輯運算符號，但是它們必須用在查詢的左邊或右邊的資料表。

如果滿足整個複雜條件，則連接行。如果不滿足條件，則根據JOIN類型，結果中仍可能包含該筆資料，我們要注意的是，如果在WHERE的從句部分中放置了相同的條件並且未滿足這些條件的話，則時常會從結果中過濾掉此筆資料。

在ON子句中的OR運算符號使用雜湊的join演算法進行運作，對於每個帶有JOIN之連接鍵(join keys)的OR參數，都會建立一個單獨的雜湊表，因此記憶體的消耗和查詢執行時間會隨著SQL語句中的OR數量與ON從句的增加呈線性成長。

要注意的是，如果條件是來自不同資料表的欄位，則目前僅支援使用相等運算符號(=)。

範例1，假設有兩個資料分別叫做table_1與table_2，其資料表綱要與資料內容如下：

┌─Id─┬─name─┐     ┌─Id─┬─text───────────┬─scores─┐
│  1 │ A    │     │  1 │ Text A         │     10 │
│  2 │ B    │     │  1 │ Another text A │     12 │
│  3 │ C    │     │  2 │ Text B         │     15 │
└────┴──────┘     └────┴────────────────┴────────┘

查詢語句使用一個連接鍵(join key)條件且在table_2的額外條件，這樣的SQL語句如下所示：

SELECT name, text FROM table_1 LEFT OUTER JOIN table_2
    ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text');

注意的是上述的結果會包含欄位名稱name是C且text欄位是空的字串的資料，這筆資料會包含在查詢結果是因為OUTER用在join的語句中。

查詢並包含INNER並用在join的語句中，且包含多個條件的SQL語句範例如下：

SELECT name, text, scores FROM table_1 INNER JOIN table_2
    ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text');

上述的查詢結果如下：

┌─name─┬─text───┬─scores─┐
│ B    │ Text B │     15 │
└──────┴────────┴────────┘

查詢並包含INNER並用在join的語句中，且在條件中使用OR的SQL語句範例如下：

CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;

CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;

INSERT INTO t1 SELECT number as a, -a as b from numbers(5);

INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key;

上述的查詢結果如下：

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 1 │ -1 │   1 │
│ 2 │ -2 │   2 │
│ 3 │ -3 │   3 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

查詢並包含INNER並用在join的語句中，且在條件中使用OR與AND的SQL語句範例如下：

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3;

上述的查詢結果如下：

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 2 │ -2 │   2 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

當我們需要連接不完全符合的資料時，使用ASOF JOIN是很有用的。

演算法需要在資料表中某些特定欄位才有作用，這些欄位需要的條件如下：

• 必須包含可以做排序的。
• 可以是這些資料欄位型別：Int、UInt、Float、Date、DateTime以及Decimal.
• 在JOIN從句中唯一的欄位。

ASOF JOIN ... ON的語法範例如下：

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

我們可以使用任意數量的相等條件以及一個最接近的符合條件。相關SQL範例如下：

SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t。

可以使用在條件的運算符號為：>、>=、<以及<=。

ASOF JOIN ... USING的語法範例如下：

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

ASOF JOIN 使用equi_columnX的連接(join)相等性和asof_column連接(join)與table_1.asof_column >= table_2.asof_column間最符合的條件。而asof_column欄位始終是在USING從句中的最後一列。

舉例來說，考慮下列這兩個資料表的綱要與資料內容如下：

     table_1                           table_2
  event   | ev_time | user_id       event   | ev_time | user_id
----------|---------|----------   ----------|---------|----------
              ...                               ...
event_1_1 |  12:00  |  42         event_2_1 |  11:59  |   42
              ...                 event_2_2 |  12:30  |   42
event_1_2 |  13:00  |  42         event_2_3 |  13:00  |   42
              ...                               ...

ASOF JOIN可以從table_1資料表中得到該使用者事件的時間，並在table_2資料表中找到最接近的時間並對應到table_1資料表的最接近符合條件的事件時間。而相等的時間是最接近的。在這裡的範例來說，user_id的欄位可用於相等的條件，而ev_time欄位則可用於最接近的符合時間。在上述的範例中，event_1_1可以與event_2_1連接(join)，event_1_2可以與 event_2_3連接(join)，但是event_2_2不能進行連接(join)，因為沒有資料與它的最接近時間相符合。

我們需要注意的是，ASOF只支援在Join的資料表引擎使用。

針對分散式的連接(JOIN)，我們有兩種方法可以執行這類型的JOIN：

• 當使用普通的連接JOIN的時候，SQL查詢會被發送到遠端的伺服器，為了建立正確的資料表，對於每一個運行的子查詢，並且使用該資料表進行連接。換句話說，正確的資料表是在每個伺服器上單獨形成的。
• 當使用GLOBAL ... JOIN的時候，首先會向請求查詢的伺服器執行一個子查詢來計算正確的資料表，這個臨時的資料表會被傳遞到每個遠端的伺服器，並透過網路傳輸將臨時資料在該資料表上進行查詢。

當使用GLOBAL時候需要小心，可以參考後續有關於分散式的子查詢(Distributed subqueries)章節。

INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN查詢支持「連接鍵」(join keys)的隱式類型轉換。即在資料表查詢的時候會將該資料欄位進行轉型，但是，如果無法將左與右資料表的連接鍵轉換為單一資料類型；例如，沒有單一欄位的資料類型可以同時保存UInt64和Int64或是String和Int32的所有數值。

相關的範例如下，假設table_1資料表綱要與資料如下：

┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐
│ 1 │ 1 │ UInt16        │ UInt8         │
│ 2 │ 2 │ UInt16        │ UInt8         │
└───┴───┴───────────────┴───────────────┘

且table_2資料表綱要與資料如下：

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │    1 │ Int16         │ Nullable(Int64) │
│  1 │   -1 │ Int16         │ Nullable(Int64) │
│  1 │    1 │ Int16         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

而查詢可以是：

SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b);

並回傳的結果為：

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│  1 │    1 │ Int32         │ Nullable(Int64) │
│  2 │    2 │ Int32         │ Nullable(Int64) │
│ -1 │    1 │ Int32         │ Nullable(Int64) │
│  1 │   -1 │ Int32         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

針對使用連接(join)上的建議也下列幾點：

• 在連接(join)資料表時，可能會出現空值，在設定檔中設定join_use_nulls來讓ClickHouse資料庫了解如何處理這些空執，如果JOIN鍵有可以是空值的字串，則不會連接至少一個鍵的值為空值NULL的資料。
• USING從句在指定的欄位中，在兩個子查詢語句中必須要有相同的欄位名稱，並且其他欄位名稱必須不同，我們可以使用別名來更改子查詢語句中欄位的名稱。
• USING從句可以指定一個或多個要連接的欄位，並從而建立起這些欄位的相等性(equality)，欄位的設定為不帶括號並不支援更複雜的連接條件。
• 對於單一的SELECT查詢中有多個JOIN的從句：
  • 僅當連接(join)資料表而不是子查詢時，才可以使用*來獲取所有的欄位。
  • PREWHERE從句不可以使用。
• 對於ON、WHERE和GROUP BY從句：
  • 任意的SQL語句不能在ON、WHERE和GROUP BY子查詢中使用，但我們可以在SELECT子句中定義表達式，接著通過別名的方式在這些子句中使用它們。
• 在執行JOIN時，在查詢的其他階段中，不會有相關的執行順序的優化；JOIN（右邊資料表中的查詢）會在WHERE進行條件篩選與聚合之前執行。
• 每次使用相同的JOIN來執行查詢時，都會再次運行子查詢，原因是查詢的結果並沒有被儲存在快取中，為避免這種情況發生，我們需要使用特殊的Join連接的資料庫引擎，它是一個準備好的連接陣列，並始終存放在記憶體中。
• 在某些情況下，使用IN比JOIN從句更有效率。
• 如果我們需要一個JOIN來連接維度表（指的是包含維度屬性的相對較小的表，例如廣告活動的名稱），一個JOIN可能不是很方便，因為每個人都需要重新查詢正確的資料表；對於這種情況，我們應該使用「External dictionaries」功能來代替JOIN，有關詳細資訊，請參考後續的章節。

在預設的情況下，ClickHouse資料庫會使用雜湊連接算法，ClickHouse資料庫取得right_table並在記憶體中中為其建立一個雜湊表，如果啟用了join_algorithm = 'auto'的設定，則在達到一定的記憶體消耗的臨界值時，ClickHouse資料庫會退回去使用合併連接算法，對於JOIN演算法描述，請參考後續有關於資料庫設定的章節。

如果我們需要限制JOIN操作的記憶體消耗，請使用以下設定：

1. max_rows_in_join — 限制雜湊表中的行數。
2. max_bytes_in_join — 限制雜湊表的大小。
3. 當上述的任一個設定達到限制的設定值時，ClickHouse資料庫將會依照join_overflow_mode設定來運作。

下面是一個left join的範例：

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

其查詢的結果會是如下：

┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘

PREWHERE clause

PREWHERE從句是一種優化的方式，並可以更有效地篩選資料，即使沒有明確的指定只用PREWHERE從句，也會默認使用它，它會通過自動將WHERE從句中，條件的一部分移動到PREWHERE方式進行工作。

如果我們知道如何比預設情況下做得更好的話，則PREWHERE從句的作用只是在控制這些優化。

使用PREWHERE優化，首先只會讀取與執行PREWHERE表達式所需的欄位，接著讀取並執行剩餘查詢所需要的欄位，但僅讀取其中有關於PREWHERE表達式至少對於某些行為需要的那些資料區塊。

如果有很多資料區塊對PREWHERE表達式對於所有資料都是false，並且PREWHERE需要的資料欄位比查詢的其他部分還要少，這通常允許從硬碟讀取更少的資料來執行查詢。

這個從句的定義與WHERE從句相同，差別在於資料是從哪一個資料表做讀取的。當我們手動控制PREWHERE從句來當作猜選條件時，只會在查詢語句中利用少數的欄位進行查詢，但是提供了強大的資料篩選方式，這將會減少要讀取的資料量。

一個查詢可能會同時指定PREWHER與WHERE從句，在這樣的情形下，PREWHERE會先執行，接著才會是WHERE從句。

如果在ClickHouse資料庫的設定檔中，設定optimize_move_to_prewhere的值為0時，執行SQL語句時，自動的將部分的描述語句，從WHERE移到PREWHERE語句的機制會是關閉的。

如果查詢語句中含有FINAL的關鍵字，PREWHERE從句的優化時常不會是正確的，這是因為同時啟用了optimize_move_to_prewhere與optimize_move_to_prewhere_if_final這兩個設定的緣故。

PREWHERE只支援MergeTree系列的資料表引擎家族。

WHERE clause

WHERE從句允許用來篩選資料，並會在SELECT FROM TABLE的語句後面。

如果SQL語句中有一個WHERE的從句，將必須包含有個描述式的欄位型別是UInt8，這個從句通常會有一個比較和邏輯的運算的描述式，若資料不被WHERE從句的表達式篩選到，即排除在查詢的結果之外，這些排除在外的資料不會包含在更進一步的查詢結果或是其他轉換(如聚合aggregate)之內。

如果該資料表引擎有支援的話，WHERE從句描述式也可以用來作為索引(indexes)的與分區篩選(partition pruning)。

如果我們需要測試一個欄位的值是否是空值NULL，可以使用IS NULL、IS NOT NULL的表達式，或者使用isNull與isNotNull的內建函數，否則的話有個描述式有空值的話則永遠不會通過該條件，即該筆資料永遠不會被篩選到。

以下為範例，為了要找到3的倍數且比10大，則可以在numbers資料表中加入WHERE從句來產生出下列的篩選SQL語句：

SELECT number FROM numbers(20) WHERE (number > 10) AND (number % 3 == 0);

執行上述的SQL語句之後，會得到下列的結果：

┌─number─┐
│     12 │
│     15 │
│     18 │
└────────┘

從上述的SQL語句中可以得知，numbers是一個ClickHouse資料庫內建的資料表函數，可以產生從N到N+M-1的數，以上述的範例來說，numbers(20)之N為0而M為20，將會產生0到19數字的資料表，N最大是UInt64(Unsigned integer 64, 無符號64位元整數)，與system.numbers資料表類似，這個函式可以用來測試或是產生一些數值的資料表，使用numbers(N, M)會比使用system.numbers來的有效率，相關的numbers資料表函數用法如下：

SELECT * FROM numbers(10);
SELECT * FROM numbers(0, 10);
SELECT * FROM system.numbers LIMIT 10;

-- 將會產生從2010-01-01到2010-12-31的日期
select toDate('2010-01-01') + number as d FROM numbers(365);

SQL查詢語句包含空值的WHERE從句與敘述式範例：

CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE=MergeTree() ORDER BY x;
INSERT INTO t_null VALUES (1, NULL), (2, 3);

SELECT * FROM t_null WHERE y IS NULL;
SELECT * FROM t_null WHERE y != 0;

執行上述的語句之後，得到的結果如下：

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
└───┴──────┘
┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘

結論

在本章節中，介紹了另外一部分的SELECT語句可以使用的從句，在下一章節中還會介紹其他的SELECT語句可以接的從句。

參考資料

• https://clickhouse.com/docs/en/sql-reference/statements/select
• https://zh.wikipedia.org/zh-tw/%E7%AC%9B%E5%8D%A1%E5%84%BF%E7%A7%AF